package com.example.teacherservice.repository;

import com.example.teacherservice.entity.*;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@Repository
public interface CourseDetailRepository extends JpaRepository<Course, Long> {

    // 获取课程章节
    @Query(value = "SELECT * FROM chapter WHERE cou_id = :courseId AND deleted = 0 ORDER BY cha_index ASC", nativeQuery = true)
    List<Chapter> findChaptersByCourseId(@Param("courseId") Long courseId);

    // 获取课程评价
    @Query(value = "SELECT * FROM stu_course_appraise WHERE course_id = :courseId AND deleted = 0", nativeQuery = true)
    List<StuCourseAppraise> findAppraisesByCourseId(@Param("courseId") Long courseId);

    // 创建订单
    @Modifying
    @Transactional
    @Query(value = "INSERT INTO stu_order(code, stu_id, amount, create_time) VALUES(:orderCode, :studentId, :amount, NOW())", nativeQuery = true)
    void insertOrder(@Param("orderCode") String orderCode,
                     @Param("studentId") Long studentId,
                     @Param("amount") Double amount);

    // 创建订单详情（根据实际表结构调整字段名）
    @Modifying
    @Transactional
    @Query(value = "INSERT INTO stu_order_detail(order_id, cou_id, cou_price, cou_pic, cou_name, tea_name) " +
            "VALUES(:orderId, :courseId, :price, :pictureUrl, :courseName, :teacherName)", nativeQuery = true)
    void insertOrderDetail(
            @Param("orderId") Long orderId,
            @Param("courseId") Long courseId,
            @Param("price") Double price,
            @Param("pictureUrl") String pictureUrl,
            @Param("courseName") String courseName,
            @Param("teacherName") String teacherName);

    // 更新课程购买数量
    @Modifying
    @Transactional
    @Query(value = "UPDATE course SET purchase = purchase + 1 WHERE id = :courseId", nativeQuery = true)
    void incrementPurchaseCount(@Param("courseId") Long courseId);

    // 获取最新创建的订单ID
    @Query(value = "SELECT LAST_INSERT_ID()", nativeQuery = true)
    Long getLastInsertId();

    // 检查购买状态
    @Query(value = "SELECT EXISTS(SELECT 1 FROM stu_course WHERE stu_id = :studentId AND course_id = :courseId)",
            nativeQuery = true)
    boolean existsByStuIdAndCourseId(@Param("studentId") Long studentId,
                                     @Param("courseId") Long courseId);

    // 检查购买状态（备选方案）
    @Query(value = "SELECT COUNT(*) FROM stu_course WHERE stu_id = :studentId AND course_id = :courseId",
            nativeQuery = true)
    int countByStuIdAndCourseId(@Param("studentId") Long studentId,
                                @Param("courseId") Long courseId);

    // 创建学生课程关联
    @Modifying
    @Transactional
    @Query(value = "INSERT INTO stu_course(stu_id, course_id, create_time) VALUES(:studentId, :courseId, NOW())",
            nativeQuery = true)
    void insertStuCourse(@Param("studentId") Long studentId,
                         @Param("courseId") Long courseId);

    // 检查购买状态（备选方案2）
    @Query(value = "SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END FROM stu_course WHERE stu_id = :studentId AND course_id = :courseId",
            nativeQuery = true)
    int existsPurchase(@Param("studentId") Long studentId,
                       @Param("courseId") Long courseId);

    // 添加评价
    @Modifying
    @Transactional
    @Query(value = "INSERT INTO stu_course_appraise(student_id, course_id, evaluate_score, score, content) " +
            "VALUES(:studentId, :courseId, :score, :score, :content)", nativeQuery = true)
    void insertCourseAppraise(
            @Param("studentId") Long studentId,
            @Param("courseId") Long courseId,
            @Param("score") String score,
            @Param("content") String content);

    // 获取最新评价ID
    @Query(value = "SELECT LAST_INSERT_ID()", nativeQuery = true)
    Long getLastAppraiseId();
}